﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class LotTemplateDA : ILotTemplateDA
    {
        private string CREATED_BY = "@CREATED_BY";
        private string DESCRIPTION = "@DESCRIPTION";
        private string EXPIRY_DATE_LABEL = "@EXPIRY_DATE_LABEL";
        private string EXPIRY_DATE_MANDATORY = "@EXPIRY_DATE_MANDATORY";
        private string EXPIRY_DATE_MANDATORY_OT = "@EXPIRY_DATE_MANDATORY_OT";
        private string EXPIRY_DATE_SEQNO = "@EXPIRY_DATE_SEQNO";
        private string EXPIRY_DATE_VISIBLE = "@EXPIRY_DATE_VISIBLE";
        private string EXTERNAL_LOT_LABEL = "@EXTERNAL_LOT_LABEL";
        private string EXTERNAL_LOT_MANDATORY = "@EXTERNAL_LOT_MANDATORY";
        private string EXTERNAL_LOT_MANDATORY_OT = "@EXTERNAL_LOT_MANDATORY_OT";
        private string EXTERNAL_LOT_SEQNO = "@EXTERNAL_LOT_SEQNO";
        private string EXTERNAL_LOT_VISIBLE = "@EXTERNAL_LOT_VISIBLE";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string LOT_ATTR01_DEFAULT_IN = "@LOT_ATTR01_DEFAULT_IN";
        private string LOT_ATTR01_DEFAULT_OT = "@LOT_ATTR01_DEFAULT_OT";
        private string LOT_ATTR01_LABEL = "@LOT_ATTR01_LABEL";
        private string LOT_ATTR01_MANDATORY = "@LOT_ATTR01_MANDATORY";
        private string LOT_ATTR01_MANDATORY_OT = "@LOT_ATTR01_MANDATORY_OT";
        private string LOT_ATTR01_SEQNO = "@LOT_ATTR01_SEQNO";
        private string LOT_ATTR01_TYPE = "@LOT_ATTR01_TYPE";
        private string LOT_ATTR01_VALUE = "@LOT_ATTR01_VALUE";
        private string LOT_ATTR01_VISIBLE = "@LOT_ATTR01_VISIBLE";
        private string LOT_ATTR02_DEFAULT_IN = "@LOT_ATTR02_DEFAULT_IN";
        private string LOT_ATTR02_DEFAULT_OT = "@LOT_ATTR02_DEFAULT_OT";
        private string LOT_ATTR02_LABEL = "@LOT_ATTR02_LABEL";
        private string LOT_ATTR02_MANDATORY = "@LOT_ATTR02_MANDATORY";
        private string LOT_ATTR02_MANDATORY_OT = "@LOT_ATTR02_MANDATORY_OT";
        private string LOT_ATTR02_SEQNO = "@LOT_ATTR02_SEQNO";
        private string LOT_ATTR02_TYPE = "@LOT_ATTR02_TYPE";
        private string LOT_ATTR02_VALUE = "@LOT_ATTR02_VALUE";
        private string LOT_ATTR02_VISIBLE = "@LOT_ATTR02_VISIBLE";
        private string LOT_ATTR03_DEFAULT_IN = "@LOT_ATTR03_DEFAULT_IN";
        private string LOT_ATTR03_DEFAULT_OT = "@LOT_ATTR03_DEFAULT_OT";
        private string LOT_ATTR03_LABEL = "@LOT_ATTR03_LABEL";
        private string LOT_ATTR03_MANDATORY = "@LOT_ATTR03_MANDATORY";
        private string LOT_ATTR03_MANDATORY_OT = "@LOT_ATTR03_MANDATORY_OT";
        private string LOT_ATTR03_SEQNO = "@LOT_ATTR03_SEQNO";
        private string LOT_ATTR03_TYPE = "@LOT_ATTR03_TYPE";
        private string LOT_ATTR03_VALUE = "@LOT_ATTR03_VALUE";
        private string LOT_ATTR03_VISIBLE = "@LOT_ATTR03_VISIBLE";
        private string LOT_ATTR04_DEFAULT_IN = "@LOT_ATTR04_DEFAULT_IN";
        private string LOT_ATTR04_DEFAULT_OT = "@LOT_ATTR04_DEFAULT_OT";
        private string LOT_ATTR04_LABEL = "@LOT_ATTR04_LABEL";
        private string LOT_ATTR04_MANDATORY = "@LOT_ATTR04_MANDATORY";
        private string LOT_ATTR04_MANDATORY_OT = "@LOT_ATTR04_MANDATORY_OT";
        private string LOT_ATTR04_SEQNO = "@LOT_ATTR04_SEQNO";
        private string LOT_ATTR04_TYPE = "@LOT_ATTR04_TYPE";
        private string LOT_ATTR04_VALUE = "@LOT_ATTR04_VALUE";
        private string LOT_ATTR04_VISIBLE = "@LOT_ATTR04_VISIBLE";
        private string LOT_ATTR05_DEFAULT_IN = "@LOT_ATTR05_DEFAULT_IN";
        private string LOT_ATTR05_DEFAULT_OT = "@LOT_ATTR05_DEFAULT_OT";
        private string LOT_ATTR05_LABEL = "@LOT_ATTR05_LABEL";
        private string LOT_ATTR05_MANDATORY = "@LOT_ATTR05_MANDATORY";
        private string LOT_ATTR05_MANDATORY_OT = "@LOT_ATTR05_MANDATORY_OT";
        private string LOT_ATTR05_SEQNO = "@LOT_ATTR05_SEQNO";
        private string LOT_ATTR05_TYPE = "@LOT_ATTR05_TYPE";
        private string LOT_ATTR05_VALUE = "@LOT_ATTR05_VALUE";
        private string LOT_ATTR05_VISIBLE = "@LOT_ATTR05_VISIBLE";
        private string LOT_ATTR06_DEFAULT_IN = "@LOT_ATTR06_DEFAULT_IN";
        private string LOT_ATTR06_DEFAULT_OT = "@LOT_ATTR06_DEFAULT_OT";
        private string LOT_ATTR06_LABEL = "@LOT_ATTR06_LABEL";
        private string LOT_ATTR06_MANDATORY = "@LOT_ATTR06_MANDATORY";
        private string LOT_ATTR06_MANDATORY_OT = "@LOT_ATTR06_MANDATORY_OT";
        private string LOT_ATTR06_SEQNO = "@LOT_ATTR06_SEQNO";
        private string LOT_ATTR06_TYPE = "@LOT_ATTR06_TYPE";
        private string LOT_ATTR06_VALUE = "@LOT_ATTR06_VALUE";
        private string LOT_ATTR06_VISIBLE = "@LOT_ATTR06_VISIBLE";
        private string LOT_ATTR07_DEFAULT_IN = "@LOT_ATTR07_DEFAULT_IN";
        private string LOT_ATTR07_DEFAULT_OT = "@LOT_ATTR07_DEFAULT_OT";
        private string LOT_ATTR07_LABEL = "@LOT_ATTR07_LABEL";
        private string LOT_ATTR07_MANDATORY = "@LOT_ATTR07_MANDATORY";
        private string LOT_ATTR07_MANDATORY_OT = "@LOT_ATTR07_MANDATORY_OT";
        private string LOT_ATTR07_SEQNO = "@LOT_ATTR07_SEQNO";
        private string LOT_ATTR07_TYPE = "@LOT_ATTR07_TYPE";
        private string LOT_ATTR07_VALUE = "@LOT_ATTR07_VALUE";
        private string LOT_ATTR07_VISIBLE = "@LOT_ATTR07_VISIBLE";
        private string LOT_ATTR08_DEFAULT_IN = "@LOT_ATTR08_DEFAULT_IN";
        private string LOT_ATTR08_DEFAULT_OT = "@LOT_ATTR08_DEFAULT_OT";
        private string LOT_ATTR08_LABEL = "@LOT_ATTR08_LABEL";
        private string LOT_ATTR08_MANDATORY = "@LOT_ATTR08_MANDATORY";
        private string LOT_ATTR08_MANDATORY_OT = "@LOT_ATTR08_MANDATORY_OT";
        private string LOT_ATTR08_SEQNO = "@LOT_ATTR08_SEQNO";
        private string LOT_ATTR08_TYPE = "@LOT_ATTR08_TYPE";
        private string LOT_ATTR08_VALUE = "@LOT_ATTR08_VALUE";
        private string LOT_ATTR08_VISIBLE = "@LOT_ATTR08_VISIBLE";
        private string LOT_ATTR09_DEFAULT_IN = "@LOT_ATTR09_DEFAULT_IN";
        private string LOT_ATTR09_DEFAULT_OT = "@LOT_ATTR09_DEFAULT_OT";
        private string LOT_ATTR09_LABEL = "@LOT_ATTR09_LABEL";
        private string LOT_ATTR09_MANDATORY = "@LOT_ATTR09_MANDATORY";
        private string LOT_ATTR09_MANDATORY_OT = "@LOT_ATTR09_MANDATORY_OT";
        private string LOT_ATTR09_SEQNO = "@LOT_ATTR09_SEQNO";
        private string LOT_ATTR09_TYPE = "@LOT_ATTR09_TYPE";
        private string LOT_ATTR09_VALUE = "@LOT_ATTR09_VALUE";
        private string LOT_ATTR09_VISIBLE = "@LOT_ATTR09_VISIBLE";
        private string LOT_ATTR10_DEFAULT_IN = "@LOT_ATTR10_DEFAULT_IN";
        private string LOT_ATTR10_DEFAULT_OT = "@LOT_ATTR10_DEFAULT_OT";
        private string LOT_ATTR10_LABEL = "@LOT_ATTR10_LABEL";
        private string LOT_ATTR10_MANDATORY = "@LOT_ATTR10_MANDATORY";
        private string LOT_ATTR10_MANDATORY_OT = "@LOT_ATTR10_MANDATORY_OT";
        private string LOT_ATTR10_SEQNO = "@LOT_ATTR10_SEQNO";
        private string LOT_ATTR10_TYPE = "@LOT_ATTR10_TYPE";
        private string LOT_ATTR10_VALUE = "@LOT_ATTR10_VALUE";
        private string LOT_ATTR10_VISIBLE = "@LOT_ATTR10_VISIBLE";
        private string LOT_TEMPLATE_ID = "@LOT_TEMPLATE_ID";
        private string PRODUCE_DATE_LABEL = "@PRODUCE_DATE_LABEL";
        private string PRODUCE_DATE_MANDATORY = "@PRODUCE_DATE_MANDATORY";
        private string PRODUCE_DATE_MANDATORY_OT = "@PRODUCE_DATE_MANDATORY_OT";
        private string PRODUCE_DATE_SEQNO = "@PRODUCE_DATE_SEQNO";
        private string PRODUCE_DATE_VISIBLE = "@PRODUCE_DATE_VISIBLE";
        private string RF_EXPIRY_DATE_VISIBLE = "@RF_EXPIRY_DATE_VISIBLE";
        private string RF_EXTERNAL_LOT_VISIBLE = "@RF_EXTERNAL_LOT_VISIBLE";
        private string RF_LOT_ATTR01_VISIBLE = "@RF_LOT_ATTR01_VISIBLE";
        private string RF_LOT_ATTR02_VISIBLE = "@RF_LOT_ATTR02_VISIBLE";
        private string RF_LOT_ATTR03_VISIBLE = "@RF_LOT_ATTR03_VISIBLE";
        private string RF_LOT_ATTR04_VISIBLE = "@RF_LOT_ATTR04_VISIBLE";
        private string RF_LOT_ATTR05_VISIBLE = "@RF_LOT_ATTR05_VISIBLE";
        private string RF_LOT_ATTR06_VISIBLE = "@RF_LOT_ATTR06_VISIBLE";
        private string RF_LOT_ATTR07_VISIBLE = "@RF_LOT_ATTR07_VISIBLE";
        private string RF_LOT_ATTR08_VISIBLE = "@RF_LOT_ATTR08_VISIBLE";
        private string RF_LOT_ATTR09_VISIBLE = "@RF_LOT_ATTR09_VISIBLE";
        private string RF_LOT_ATTR10_VISIBLE = "@RF_LOT_ATTR10_VISIBLE";
        private string RF_PRODUCE_DATE_VISIBLE = "@RF_PRODUCE_DATE_VISIBLE";
        private string SQL_CHECK_LOT_TEMPLATE_ID_UNIQUE = " SELECT COUNT(1) FROM @_@LOT_TEMPLATE WHERE LOT_TEMPLATE_ID = @LOT_TEMPLATE_ID ";
        private string SQL_DELETE_LOT_TEMPLATE = " DELETE FROM @_@LOT_TEMPLATE WHERE LOT_TEMPLATE_ID = @LOT_TEMPLATE_ID ";
        private string SQL_INSERT_LOT_TEMPLATE = " INSERT INTO @_@LOT_TEMPLATE ( WH_ID, LOT_TEMPLATE_ID, DESCRIPTION, PRODUCE_DATE_VISIBLE, PRODUCE_DATE_MANDATORY, EXPIRY_DATE_VISIBLE, EXPIRY_DATE_MANDATORY, EXTERNAL_LOT_VISIBLE, EXTERNAL_LOT_MANDATORY, LOT_ATTR01_LABEL, LOT_ATTR02_LABEL, LOT_ATTR03_LABEL, LOT_ATTR04_LABEL, LOT_ATTR05_LABEL, LOT_ATTR06_LABEL, LOT_ATTR07_LABEL, LOT_ATTR08_LABEL, LOT_ATTR09_LABEL, LOT_ATTR10_LABEL, PRODUCE_DATE_LABEL, EXPIRY_DATE_LABEL, EXTERNAL_LOT_LABEL, LOT_ATTR01_VISIBLE, LOT_ATTR02_VISIBLE, LOT_ATTR03_VISIBLE, LOT_ATTR04_VISIBLE, LOT_ATTR05_VISIBLE, LOT_ATTR06_VISIBLE, LOT_ATTR07_VISIBLE, LOT_ATTR08_VISIBLE, LOT_ATTR09_VISIBLE, LOT_ATTR10_VISIBLE, LOT_ATTR01_MANDATORY, LOT_ATTR02_MANDATORY, LOT_ATTR03_MANDATORY, LOT_ATTR04_MANDATORY, LOT_ATTR05_MANDATORY, LOT_ATTR06_MANDATORY, LOT_ATTR07_MANDATORY, LOT_ATTR08_MANDATORY, LOT_ATTR09_MANDATORY, LOT_ATTR10_MANDATORY, LOT_ATTR01_TYPE, LOT_ATTR02_TYPE, LOT_ATTR03_TYPE, LOT_ATTR04_TYPE, LOT_ATTR05_TYPE, LOT_ATTR06_TYPE, LOT_ATTR07_TYPE, LOT_ATTR08_TYPE, LOT_ATTR09_TYPE, LOT_ATTR10_TYPE, LOT_ATTR01_VALUE, LOT_ATTR02_VALUE, LOT_ATTR03_VALUE, LOT_ATTR04_VALUE, LOT_ATTR05_VALUE, LOT_ATTR06_VALUE, LOT_ATTR07_VALUE, LOT_ATTR08_VALUE, LOT_ATTR09_VALUE, LOT_ATTR10_VALUE, LOT_ATTR01_SEQNO, LOT_ATTR02_SEQNO, LOT_ATTR03_SEQNO, LOT_ATTR04_SEQNO, LOT_ATTR05_SEQNO, LOT_ATTR06_SEQNO, LOT_ATTR07_SEQNO, LOT_ATTR08_SEQNO, LOT_ATTR09_SEQNO, LOT_ATTR10_SEQNO, PRODUCE_DATE_SEQNO, EXPIRY_DATE_SEQNO, EXTERNAL_LOT_SEQNO, IS_ACTIVE, SYSTEM_CREATED, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE, RF_LOT_ATTR01_VISIBLE, RF_LOT_ATTR02_VISIBLE, RF_LOT_ATTR03_VISIBLE, RF_LOT_ATTR04_VISIBLE, RF_LOT_ATTR05_VISIBLE, RF_LOT_ATTR06_VISIBLE, RF_LOT_ATTR07_VISIBLE, RF_LOT_ATTR08_VISIBLE, RF_LOT_ATTR09_VISIBLE, RF_LOT_ATTR10_VISIBLE, RF_PRODUCE_DATE_VISIBLE, RF_EXPIRY_DATE_VISIBLE, RF_EXTERNAL_LOT_VISIBLE, LOT_ATTR01_MANDATORY_OT, LOT_ATTR02_MANDATORY_OT, LOT_ATTR03_MANDATORY_OT, LOT_ATTR04_MANDATORY_OT, LOT_ATTR05_MANDATORY_OT, LOT_ATTR06_MANDATORY_OT, LOT_ATTR07_MANDATORY_OT, LOT_ATTR08_MANDATORY_OT, LOT_ATTR09_MANDATORY_OT, LOT_ATTR10_MANDATORY_OT, PRODUCE_DATE_MANDATORY_OT, EXPIRY_DATE_MANDATORY_OT, EXTERNAL_LOT_MANDATORY_OT, LOT_ATTR01_DEFAULT_IN, LOT_ATTR02_DEFAULT_IN, LOT_ATTR03_DEFAULT_IN, LOT_ATTR04_DEFAULT_IN, LOT_ATTR05_DEFAULT_IN, LOT_ATTR06_DEFAULT_IN, LOT_ATTR07_DEFAULT_IN, LOT_ATTR08_DEFAULT_IN, LOT_ATTR09_DEFAULT_IN, LOT_ATTR10_DEFAULT_IN, LOT_ATTR01_DEFAULT_OT, LOT_ATTR02_DEFAULT_OT, LOT_ATTR03_DEFAULT_OT, LOT_ATTR04_DEFAULT_OT, LOT_ATTR05_DEFAULT_OT, LOT_ATTR06_DEFAULT_OT, LOT_ATTR07_DEFAULT_OT, LOT_ATTR08_DEFAULT_OT, LOT_ATTR09_DEFAULT_OT, LOT_ATTR10_DEFAULT_OT) VALUES ( @WH_ID, @LOT_TEMPLATE_ID, @DESCRIPTION, @PRODUCE_DATE_VISIBLE, @PRODUCE_DATE_MANDATORY, @EXPIRY_DATE_VISIBLE, @EXPIRY_DATE_MANDATORY, @EXTERNAL_LOT_VISIBLE, @EXTERNAL_LOT_MANDATORY, @LOT_ATTR01_LABEL, @LOT_ATTR02_LABEL, @LOT_ATTR03_LABEL, @LOT_ATTR04_LABEL, @LOT_ATTR05_LABEL, @LOT_ATTR06_LABEL, @LOT_ATTR07_LABEL, @LOT_ATTR08_LABEL, @LOT_ATTR09_LABEL, @LOT_ATTR10_LABEL, @PRODUCE_DATE_LABEL, @EXPIRY_DATE_LABEL, @EXTERNAL_LOT_LABEL, @LOT_ATTR01_VISIBLE, @LOT_ATTR02_VISIBLE, @LOT_ATTR03_VISIBLE, @LOT_ATTR04_VISIBLE, @LOT_ATTR05_VISIBLE, @LOT_ATTR06_VISIBLE, @LOT_ATTR07_VISIBLE, @LOT_ATTR08_VISIBLE, @LOT_ATTR09_VISIBLE, @LOT_ATTR10_VISIBLE, @LOT_ATTR01_MANDATORY, @LOT_ATTR02_MANDATORY, @LOT_ATTR03_MANDATORY, @LOT_ATTR04_MANDATORY, @LOT_ATTR05_MANDATORY, @LOT_ATTR06_MANDATORY, @LOT_ATTR07_MANDATORY, @LOT_ATTR08_MANDATORY, @LOT_ATTR09_MANDATORY, @LOT_ATTR10_MANDATORY, @LOT_ATTR01_TYPE, @LOT_ATTR02_TYPE, @LOT_ATTR03_TYPE, @LOT_ATTR04_TYPE, @LOT_ATTR05_TYPE, @LOT_ATTR06_TYPE, @LOT_ATTR07_TYPE, @LOT_ATTR08_TYPE, @LOT_ATTR09_TYPE, @LOT_ATTR10_TYPE, @LOT_ATTR01_VALUE, @LOT_ATTR02_VALUE, @LOT_ATTR03_VALUE, @LOT_ATTR04_VALUE, @LOT_ATTR05_VALUE, @LOT_ATTR06_VALUE, @LOT_ATTR07_VALUE, @LOT_ATTR08_VALUE, @LOT_ATTR09_VALUE, @LOT_ATTR10_VALUE, @LOT_ATTR01_SEQNO, @LOT_ATTR02_SEQNO, @LOT_ATTR03_SEQNO, @LOT_ATTR04_SEQNO, @LOT_ATTR05_SEQNO, @LOT_ATTR06_SEQNO, @LOT_ATTR07_SEQNO, @LOT_ATTR08_SEQNO, @LOT_ATTR09_SEQNO, @LOT_ATTR10_SEQNO, @PRODUCE_DATE_SEQNO, @EXPIRY_DATE_SEQNO, @EXTERNAL_LOT_SEQNO, @IS_ACTIVE, @SYSTEM_CREATED, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE(), @RF_LOT_ATTR01_VISIBLE, @RF_LOT_ATTR02_VISIBLE, @RF_LOT_ATTR03_VISIBLE, @RF_LOT_ATTR04_VISIBLE, @RF_LOT_ATTR05_VISIBLE, @RF_LOT_ATTR06_VISIBLE, @RF_LOT_ATTR07_VISIBLE, @RF_LOT_ATTR08_VISIBLE, @RF_LOT_ATTR09_VISIBLE, @RF_LOT_ATTR10_VISIBLE, @RF_PRODUCE_DATE_VISIBLE, @RF_EXPIRY_DATE_VISIBLE, @RF_EXTERNAL_LOT_VISIBLE, @LOT_ATTR01_MANDATORY_OT, @LOT_ATTR02_MANDATORY_OT, @LOT_ATTR03_MANDATORY_OT, @LOT_ATTR04_MANDATORY_OT, @LOT_ATTR05_MANDATORY_OT, @LOT_ATTR06_MANDATORY_OT, @LOT_ATTR07_MANDATORY_OT, @LOT_ATTR08_MANDATORY_OT, @LOT_ATTR09_MANDATORY_OT, @LOT_ATTR10_MANDATORY_OT, @PRODUCE_DATE_MANDATORY_OT, @EXPIRY_DATE_MANDATORY_OT, @EXTERNAL_LOT_MANDATORY_OT, @LOT_ATTR01_DEFAULT_IN, @LOT_ATTR02_DEFAULT_IN, @LOT_ATTR03_DEFAULT_IN, @LOT_ATTR04_DEFAULT_IN, @LOT_ATTR05_DEFAULT_IN, @LOT_ATTR06_DEFAULT_IN, @LOT_ATTR07_DEFAULT_IN, @LOT_ATTR08_DEFAULT_IN, @LOT_ATTR09_DEFAULT_IN, @LOT_ATTR10_DEFAULT_IN, @LOT_ATTR01_DEFAULT_OT, @LOT_ATTR02_DEFAULT_OT, @LOT_ATTR03_DEFAULT_OT, @LOT_ATTR04_DEFAULT_OT, @LOT_ATTR05_DEFAULT_OT, @LOT_ATTR06_DEFAULT_OT, @LOT_ATTR07_DEFAULT_OT, @LOT_ATTR08_DEFAULT_OT, @LOT_ATTR09_DEFAULT_OT, @LOT_ATTR10_DEFAULT_OT) ";
        private string SQL_SELECT_ALL_LOT_TEMPLATE = " SELECT  ROWID, WH_ID, LOT_TEMPLATE_ID, DESCRIPTION, PRODUCE_DATE_VISIBLE, PRODUCE_DATE_MANDATORY, EXPIRY_DATE_VISIBLE, EXPIRY_DATE_MANDATORY, EXTERNAL_LOT_VISIBLE, EXTERNAL_LOT_MANDATORY, LOT_ATTR01_LABEL, LOT_ATTR02_LABEL, LOT_ATTR03_LABEL, LOT_ATTR04_LABEL, LOT_ATTR05_LABEL, LOT_ATTR06_LABEL, LOT_ATTR07_LABEL, LOT_ATTR08_LABEL, LOT_ATTR09_LABEL, LOT_ATTR10_LABEL, PRODUCE_DATE_LABEL, EXPIRY_DATE_LABEL, EXTERNAL_LOT_LABEL, LOT_ATTR01_VISIBLE, LOT_ATTR02_VISIBLE, LOT_ATTR03_VISIBLE, LOT_ATTR04_VISIBLE, LOT_ATTR05_VISIBLE, LOT_ATTR06_VISIBLE, LOT_ATTR07_VISIBLE, LOT_ATTR08_VISIBLE, LOT_ATTR09_VISIBLE, LOT_ATTR10_VISIBLE, LOT_ATTR01_MANDATORY, LOT_ATTR02_MANDATORY, LOT_ATTR03_MANDATORY, LOT_ATTR04_MANDATORY, LOT_ATTR05_MANDATORY, LOT_ATTR06_MANDATORY, LOT_ATTR07_MANDATORY, LOT_ATTR08_MANDATORY, LOT_ATTR09_MANDATORY, LOT_ATTR10_MANDATORY, LOT_ATTR01_TYPE, LOT_ATTR02_TYPE, LOT_ATTR03_TYPE, LOT_ATTR04_TYPE, LOT_ATTR05_TYPE, LOT_ATTR06_TYPE, LOT_ATTR07_TYPE, LOT_ATTR08_TYPE, LOT_ATTR09_TYPE, LOT_ATTR10_TYPE, LOT_ATTR01_VALUE, LOT_ATTR02_VALUE, LOT_ATTR03_VALUE, LOT_ATTR04_VALUE, LOT_ATTR05_VALUE, LOT_ATTR06_VALUE, LOT_ATTR07_VALUE, LOT_ATTR08_VALUE, LOT_ATTR09_VALUE, LOT_ATTR10_VALUE, LOT_ATTR01_SEQNO, LOT_ATTR02_SEQNO, LOT_ATTR03_SEQNO, LOT_ATTR04_SEQNO, LOT_ATTR05_SEQNO, LOT_ATTR06_SEQNO, LOT_ATTR07_SEQNO, LOT_ATTR08_SEQNO, LOT_ATTR09_SEQNO, LOT_ATTR10_SEQNO, PRODUCE_DATE_SEQNO, EXPIRY_DATE_SEQNO, EXTERNAL_LOT_SEQNO, IS_ACTIVE, SYSTEM_CREATED, TS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE, RF_LOT_ATTR01_VISIBLE, RF_LOT_ATTR02_VISIBLE, RF_LOT_ATTR03_VISIBLE, RF_LOT_ATTR04_VISIBLE, RF_LOT_ATTR05_VISIBLE, RF_LOT_ATTR06_VISIBLE, RF_LOT_ATTR07_VISIBLE, RF_LOT_ATTR08_VISIBLE, RF_LOT_ATTR09_VISIBLE, RF_LOT_ATTR10_VISIBLE, RF_PRODUCE_DATE_VISIBLE, RF_EXPIRY_DATE_VISIBLE, RF_EXTERNAL_LOT_VISIBLE, LOT_ATTR01_MANDATORY_OT, LOT_ATTR02_MANDATORY_OT, LOT_ATTR03_MANDATORY_OT, LOT_ATTR04_MANDATORY_OT, LOT_ATTR05_MANDATORY_OT, LOT_ATTR06_MANDATORY_OT, LOT_ATTR07_MANDATORY_OT, LOT_ATTR08_MANDATORY_OT, LOT_ATTR09_MANDATORY_OT, LOT_ATTR10_MANDATORY_OT, PRODUCE_DATE_MANDATORY_OT, EXPIRY_DATE_MANDATORY_OT, EXTERNAL_LOT_MANDATORY_OT, LOT_ATTR01_DEFAULT_IN, LOT_ATTR02_DEFAULT_IN, LOT_ATTR03_DEFAULT_IN, LOT_ATTR04_DEFAULT_IN, LOT_ATTR05_DEFAULT_IN, LOT_ATTR06_DEFAULT_IN, LOT_ATTR07_DEFAULT_IN, LOT_ATTR08_DEFAULT_IN, LOT_ATTR09_DEFAULT_IN, LOT_ATTR10_DEFAULT_IN, LOT_ATTR01_DEFAULT_OT, LOT_ATTR02_DEFAULT_OT, LOT_ATTR03_DEFAULT_OT, LOT_ATTR04_DEFAULT_OT, LOT_ATTR05_DEFAULT_OT, LOT_ATTR06_DEFAULT_OT, LOT_ATTR07_DEFAULT_OT, LOT_ATTR08_DEFAULT_OT, LOT_ATTR09_DEFAULT_OT, LOT_ATTR10_DEFAULT_OT  FROM  @_@LOT_TEMPLATE ";
        private string SQL_UPDATE_LOT_TEMPLATE = " UPDATE @_@LOT_TEMPLATE SET WH_ID = @WH_ID, DESCRIPTION = @DESCRIPTION, PRODUCE_DATE_VISIBLE = @PRODUCE_DATE_VISIBLE, PRODUCE_DATE_MANDATORY = @PRODUCE_DATE_MANDATORY, EXPIRY_DATE_VISIBLE = @EXPIRY_DATE_VISIBLE, EXPIRY_DATE_MANDATORY = @EXPIRY_DATE_MANDATORY, EXTERNAL_LOT_VISIBLE = @EXTERNAL_LOT_VISIBLE, EXTERNAL_LOT_MANDATORY = @EXTERNAL_LOT_MANDATORY, LOT_ATTR01_LABEL = @LOT_ATTR01_LABEL, LOT_ATTR02_LABEL = @LOT_ATTR02_LABEL, LOT_ATTR03_LABEL = @LOT_ATTR03_LABEL, LOT_ATTR04_LABEL = @LOT_ATTR04_LABEL, LOT_ATTR05_LABEL = @LOT_ATTR05_LABEL, LOT_ATTR06_LABEL = @LOT_ATTR06_LABEL, LOT_ATTR07_LABEL = @LOT_ATTR07_LABEL, LOT_ATTR08_LABEL = @LOT_ATTR08_LABEL, LOT_ATTR09_LABEL = @LOT_ATTR09_LABEL, LOT_ATTR10_LABEL = @LOT_ATTR10_LABEL, PRODUCE_DATE_LABEL = @PRODUCE_DATE_LABEL, EXPIRY_DATE_LABEL = @EXPIRY_DATE_LABEL, EXTERNAL_LOT_LABEL = @EXTERNAL_LOT_LABEL, LOT_ATTR01_VISIBLE = @LOT_ATTR01_VISIBLE, LOT_ATTR02_VISIBLE = @LOT_ATTR02_VISIBLE, LOT_ATTR03_VISIBLE = @LOT_ATTR03_VISIBLE, LOT_ATTR04_VISIBLE = @LOT_ATTR04_VISIBLE, LOT_ATTR05_VISIBLE = @LOT_ATTR05_VISIBLE, LOT_ATTR06_VISIBLE = @LOT_ATTR06_VISIBLE, LOT_ATTR07_VISIBLE = @LOT_ATTR07_VISIBLE, LOT_ATTR08_VISIBLE = @LOT_ATTR08_VISIBLE, LOT_ATTR09_VISIBLE = @LOT_ATTR09_VISIBLE, LOT_ATTR10_VISIBLE = @LOT_ATTR10_VISIBLE, LOT_ATTR01_MANDATORY = @LOT_ATTR01_MANDATORY, LOT_ATTR02_MANDATORY = @LOT_ATTR02_MANDATORY, LOT_ATTR03_MANDATORY = @LOT_ATTR03_MANDATORY, LOT_ATTR04_MANDATORY = @LOT_ATTR04_MANDATORY, LOT_ATTR05_MANDATORY = @LOT_ATTR05_MANDATORY, LOT_ATTR06_MANDATORY = @LOT_ATTR06_MANDATORY, LOT_ATTR07_MANDATORY = @LOT_ATTR07_MANDATORY, LOT_ATTR08_MANDATORY = @LOT_ATTR08_MANDATORY, LOT_ATTR09_MANDATORY = @LOT_ATTR09_MANDATORY, LOT_ATTR10_MANDATORY = @LOT_ATTR10_MANDATORY, LOT_ATTR01_TYPE = @LOT_ATTR01_TYPE, LOT_ATTR02_TYPE = @LOT_ATTR02_TYPE, LOT_ATTR03_TYPE = @LOT_ATTR03_TYPE, LOT_ATTR04_TYPE = @LOT_ATTR04_TYPE, LOT_ATTR05_TYPE = @LOT_ATTR05_TYPE, LOT_ATTR06_TYPE = @LOT_ATTR06_TYPE, LOT_ATTR07_TYPE = @LOT_ATTR07_TYPE, LOT_ATTR08_TYPE = @LOT_ATTR08_TYPE, LOT_ATTR09_TYPE = @LOT_ATTR09_TYPE, LOT_ATTR10_TYPE = @LOT_ATTR10_TYPE, LOT_ATTR01_VALUE = @LOT_ATTR01_VALUE, LOT_ATTR02_VALUE = @LOT_ATTR02_VALUE, LOT_ATTR03_VALUE = @LOT_ATTR03_VALUE, LOT_ATTR04_VALUE = @LOT_ATTR04_VALUE, LOT_ATTR05_VALUE = @LOT_ATTR05_VALUE, LOT_ATTR06_VALUE = @LOT_ATTR06_VALUE, LOT_ATTR07_VALUE = @LOT_ATTR07_VALUE, LOT_ATTR08_VALUE = @LOT_ATTR08_VALUE, LOT_ATTR09_VALUE = @LOT_ATTR09_VALUE, LOT_ATTR10_VALUE = @LOT_ATTR10_VALUE, LOT_ATTR01_SEQNO = @LOT_ATTR01_SEQNO, LOT_ATTR02_SEQNO = @LOT_ATTR02_SEQNO, LOT_ATTR03_SEQNO = @LOT_ATTR03_SEQNO, LOT_ATTR04_SEQNO = @LOT_ATTR04_SEQNO, LOT_ATTR05_SEQNO = @LOT_ATTR05_SEQNO, LOT_ATTR06_SEQNO = @LOT_ATTR06_SEQNO, LOT_ATTR07_SEQNO = @LOT_ATTR07_SEQNO, LOT_ATTR08_SEQNO = @LOT_ATTR08_SEQNO, LOT_ATTR09_SEQNO = @LOT_ATTR09_SEQNO, LOT_ATTR10_SEQNO = @LOT_ATTR10_SEQNO, PRODUCE_DATE_SEQNO = @PRODUCE_DATE_SEQNO, EXPIRY_DATE_SEQNO = @EXPIRY_DATE_SEQNO, EXTERNAL_LOT_SEQNO = @EXTERNAL_LOT_SEQNO, IS_ACTIVE = @IS_ACTIVE, SYSTEM_CREATED = @SYSTEM_CREATED, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(), RF_LOT_ATTR01_VISIBLE = @RF_LOT_ATTR01_VISIBLE, RF_LOT_ATTR02_VISIBLE = @RF_LOT_ATTR02_VISIBLE, RF_LOT_ATTR03_VISIBLE = @RF_LOT_ATTR03_VISIBLE, RF_LOT_ATTR04_VISIBLE = @RF_LOT_ATTR04_VISIBLE, RF_LOT_ATTR05_VISIBLE = @RF_LOT_ATTR05_VISIBLE, RF_LOT_ATTR06_VISIBLE = @RF_LOT_ATTR06_VISIBLE, RF_LOT_ATTR07_VISIBLE = @RF_LOT_ATTR07_VISIBLE, RF_LOT_ATTR08_VISIBLE = @RF_LOT_ATTR08_VISIBLE, RF_LOT_ATTR09_VISIBLE = @RF_LOT_ATTR09_VISIBLE, RF_LOT_ATTR10_VISIBLE = @RF_LOT_ATTR10_VISIBLE, RF_PRODUCE_DATE_VISIBLE = @RF_PRODUCE_DATE_VISIBLE, RF_EXPIRY_DATE_VISIBLE = @RF_EXPIRY_DATE_VISIBLE, RF_EXTERNAL_LOT_VISIBLE = @RF_EXTERNAL_LOT_VISIBLE, LOT_ATTR01_MANDATORY_OT=@LOT_ATTR01_MANDATORY_OT, LOT_ATTR02_MANDATORY_OT=@LOT_ATTR02_MANDATORY_OT, LOT_ATTR03_MANDATORY_OT=@LOT_ATTR03_MANDATORY_OT, LOT_ATTR04_MANDATORY_OT=@LOT_ATTR04_MANDATORY_OT, LOT_ATTR05_MANDATORY_OT=@LOT_ATTR05_MANDATORY_OT, LOT_ATTR06_MANDATORY_OT=@LOT_ATTR06_MANDATORY_OT, LOT_ATTR07_MANDATORY_OT=@LOT_ATTR07_MANDATORY_OT, LOT_ATTR08_MANDATORY_OT=@LOT_ATTR08_MANDATORY_OT, LOT_ATTR09_MANDATORY_OT=@LOT_ATTR09_MANDATORY_OT, LOT_ATTR10_MANDATORY_OT=@LOT_ATTR10_MANDATORY_OT, PRODUCE_DATE_MANDATORY_OT=@PRODUCE_DATE_MANDATORY_OT, EXPIRY_DATE_MANDATORY_OT=@EXPIRY_DATE_MANDATORY_OT, EXTERNAL_LOT_MANDATORY_OT=@EXTERNAL_LOT_MANDATORY_OT, LOT_ATTR01_DEFAULT_IN=@LOT_ATTR01_DEFAULT_IN, LOT_ATTR02_DEFAULT_IN=@LOT_ATTR02_DEFAULT_IN, LOT_ATTR03_DEFAULT_IN=@LOT_ATTR03_DEFAULT_IN, LOT_ATTR04_DEFAULT_IN=@LOT_ATTR04_DEFAULT_IN, LOT_ATTR05_DEFAULT_IN=@LOT_ATTR05_DEFAULT_IN, LOT_ATTR06_DEFAULT_IN=@LOT_ATTR06_DEFAULT_IN, LOT_ATTR07_DEFAULT_IN=@LOT_ATTR07_DEFAULT_IN, LOT_ATTR08_DEFAULT_IN=@LOT_ATTR08_DEFAULT_IN, LOT_ATTR09_DEFAULT_IN=@LOT_ATTR09_DEFAULT_IN, LOT_ATTR10_DEFAULT_IN=@LOT_ATTR10_DEFAULT_IN, LOT_ATTR01_DEFAULT_OT=@LOT_ATTR01_DEFAULT_OT, LOT_ATTR02_DEFAULT_OT=@LOT_ATTR02_DEFAULT_OT, LOT_ATTR03_DEFAULT_OT=@LOT_ATTR03_DEFAULT_OT, LOT_ATTR04_DEFAULT_OT=@LOT_ATTR04_DEFAULT_OT, LOT_ATTR05_DEFAULT_OT=@LOT_ATTR05_DEFAULT_OT, LOT_ATTR06_DEFAULT_OT=@LOT_ATTR06_DEFAULT_OT, LOT_ATTR07_DEFAULT_OT=@LOT_ATTR07_DEFAULT_OT, LOT_ATTR08_DEFAULT_OT=@LOT_ATTR08_DEFAULT_OT, LOT_ATTR09_DEFAULT_OT=@LOT_ATTR09_DEFAULT_OT, LOT_ATTR10_DEFAULT_OT=@LOT_ATTR10_DEFAULT_OT WHERE LOT_TEMPLATE_ID = @LOT_TEMPLATE_ID ";
        private string SYSTEM_CREATED = "@SYSTEM_CREATED";
        private string TS = "@TS";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckLotTemplateIDUnique(string lotTemplateID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT_TEMPLATE_ID", lotTemplateID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_LOT_TEMPLATE_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckLotTemplateIDUnique(DataBase dataBase, DbTransaction tran, string lotTemplateID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT_TEMPLATE_ID", lotTemplateID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_LOT_TEMPLATE_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteLotTemplate(List<string> lotTemplateID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < lotTemplateID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT_TEMPLATE_ID", lotTemplateID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_LOT_TEMPLATE.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteLotTemplate(DataBase dataBase, DbTransaction tran, List<string> lotTemplateID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < lotTemplateID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT_TEMPLATE_ID", lotTemplateID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_LOT_TEMPLATE.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllLotTemplate(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_LOT_TEMPLATE.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllLotTemplate(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_LOT_TEMPLATE.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public LotTemplateInfo GetLotTemplateByID(string lottemplateID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_LOT_TEMPLATE.Replace("@_@", whLoginID) + " WHERE LOT_TEMPLATE_ID = @LOT_TEMPLATE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT_TEMPLATE_ID", lottemplateID) };
            LotTemplateInfo lotTemplateInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    lotTemplateInfo = new LotTemplateInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOT_TEMPLATE_ID"].ToString(), reader["DESCRIPTION"].ToString(), reader["PRODUCE_DATE_VISIBLE"].ToString(), reader["PRODUCE_DATE_MANDATORY"].ToString(), reader["EXPIRY_DATE_VISIBLE"].ToString(), reader["EXPIRY_DATE_MANDATORY"].ToString(), reader["EXTERNAL_LOT_VISIBLE"].ToString(), reader["EXTERNAL_LOT_MANDATORY"].ToString(), reader["LOT_ATTR01_LABEL"].ToString(), reader["LOT_ATTR02_LABEL"].ToString(), reader["LOT_ATTR03_LABEL"].ToString(), reader["LOT_ATTR04_LABEL"].ToString(), reader["LOT_ATTR05_LABEL"].ToString(), reader["LOT_ATTR06_LABEL"].ToString(), reader["LOT_ATTR07_LABEL"].ToString(), reader["LOT_ATTR08_LABEL"].ToString(), reader["LOT_ATTR09_LABEL"].ToString(), reader["LOT_ATTR10_LABEL"].ToString(), reader["PRODUCE_DATE_LABEL"].ToString(), reader["EXPIRY_DATE_LABEL"].ToString(), reader["EXTERNAL_LOT_LABEL"].ToString(), reader["LOT_ATTR01_VISIBLE"].ToString(), reader["LOT_ATTR02_VISIBLE"].ToString(), reader["LOT_ATTR03_VISIBLE"].ToString(), reader["LOT_ATTR04_VISIBLE"].ToString(), reader["LOT_ATTR05_VISIBLE"].ToString(), reader["LOT_ATTR06_VISIBLE"].ToString(), reader["LOT_ATTR07_VISIBLE"].ToString(), reader["LOT_ATTR08_VISIBLE"].ToString(), reader["LOT_ATTR09_VISIBLE"].ToString(), reader["LOT_ATTR10_VISIBLE"].ToString(), reader["LOT_ATTR01_MANDATORY"].ToString(), reader["LOT_ATTR02_MANDATORY"].ToString(), reader["LOT_ATTR03_MANDATORY"].ToString(), reader["LOT_ATTR04_MANDATORY"].ToString(), reader["LOT_ATTR05_MANDATORY"].ToString(), reader["LOT_ATTR06_MANDATORY"].ToString(), reader["LOT_ATTR07_MANDATORY"].ToString(), reader["LOT_ATTR08_MANDATORY"].ToString(), reader["LOT_ATTR09_MANDATORY"].ToString(), reader["LOT_ATTR10_MANDATORY"].ToString(), reader["LOT_ATTR01_TYPE"].ToString(), reader["LOT_ATTR02_TYPE"].ToString(), reader["LOT_ATTR03_TYPE"].ToString(), reader["LOT_ATTR04_TYPE"].ToString(), reader["LOT_ATTR05_TYPE"].ToString(), reader["LOT_ATTR06_TYPE"].ToString(), reader["LOT_ATTR07_TYPE"].ToString(), reader["LOT_ATTR08_TYPE"].ToString(), reader["LOT_ATTR09_TYPE"].ToString(), reader["LOT_ATTR10_TYPE"].ToString(), reader["LOT_ATTR01_VALUE"].ToString(), reader["LOT_ATTR02_VALUE"].ToString(), reader["LOT_ATTR03_VALUE"].ToString(), reader["LOT_ATTR04_VALUE"].ToString(), reader["LOT_ATTR05_VALUE"].ToString(), reader["LOT_ATTR06_VALUE"].ToString(), reader["LOT_ATTR07_VALUE"].ToString(), reader["LOT_ATTR08_VALUE"].ToString(), reader["LOT_ATTR09_VALUE"].ToString(), reader["LOT_ATTR10_VALUE"].ToString(), (reader["LOT_ATTR01_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR01_SEQNO"].ToString()) : 0, (reader["LOT_ATTR02_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR02_SEQNO"].ToString()) : 0, (reader["LOT_ATTR03_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR03_SEQNO"].ToString()) : 0, (reader["LOT_ATTR04_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR04_SEQNO"].ToString()) : 0, (reader["LOT_ATTR05_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR05_SEQNO"].ToString()) : 0, (reader["LOT_ATTR06_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR06_SEQNO"].ToString()) : 0, (reader["LOT_ATTR07_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR07_SEQNO"].ToString()) : 0, (reader["LOT_ATTR08_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR08_SEQNO"].ToString()) : 0, (reader["LOT_ATTR09_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR09_SEQNO"].ToString()) : 0, (reader["LOT_ATTR10_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR10_SEQNO"].ToString()) : 0, (reader["PRODUCE_DATE_SEQNO"].ToString() != "") ? int.Parse(reader["PRODUCE_DATE_SEQNO"].ToString()) : 0, (reader["EXPIRY_DATE_SEQNO"].ToString() != "") ? int.Parse(reader["EXPIRY_DATE_SEQNO"].ToString()) : 0, (reader["EXTERNAL_LOT_SEQNO"].ToString() != "") ? int.Parse(reader["EXTERNAL_LOT_SEQNO"].ToString()) : 0, reader["IS_ACTIVE"].ToString(), reader["SYSTEM_CREATED"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["RF_LOT_ATTR01_VISIBLE"].ToString(), reader["RF_LOT_ATTR02_VISIBLE"].ToString(), reader["RF_LOT_ATTR03_VISIBLE"].ToString(), reader["RF_LOT_ATTR04_VISIBLE"].ToString(), reader["RF_LOT_ATTR05_VISIBLE"].ToString(), reader["RF_LOT_ATTR06_VISIBLE"].ToString(), reader["RF_LOT_ATTR07_VISIBLE"].ToString(), reader["RF_LOT_ATTR08_VISIBLE"].ToString(), reader["RF_LOT_ATTR09_VISIBLE"].ToString(), reader["RF_LOT_ATTR10_VISIBLE"].ToString(), reader["RF_PRODUCE_DATE_VISIBLE"].ToString(), reader["RF_EXPIRY_DATE_VISIBLE"].ToString(), reader["RF_EXTERNAL_LOT_VISIBLE"].ToString(), reader["LOT_ATTR01_MANDATORY_OT"].ToString(), reader["LOT_ATTR02_MANDATORY_OT"].ToString(), reader["LOT_ATTR03_MANDATORY_OT"].ToString(), reader["LOT_ATTR04_MANDATORY_OT"].ToString(), reader["LOT_ATTR05_MANDATORY_OT"].ToString(), reader["LOT_ATTR06_MANDATORY_OT"].ToString(), reader["LOT_ATTR07_MANDATORY_OT"].ToString(), reader["LOT_ATTR08_MANDATORY_OT"].ToString(), reader["LOT_ATTR09_MANDATORY_OT"].ToString(), reader["LOT_ATTR10_MANDATORY_OT"].ToString(), reader["PRODUCE_DATE_MANDATORY_OT"].ToString(), reader["EXPIRY_DATE_MANDATORY_OT"].ToString(), reader["EXTERNAL_LOT_MANDATORY_OT"].ToString(), reader["LOT_ATTR01_DEFAULT_IN"].ToString(), reader["LOT_ATTR02_DEFAULT_IN"].ToString(), reader["LOT_ATTR03_DEFAULT_IN"].ToString(), reader["LOT_ATTR04_DEFAULT_IN"].ToString(), reader["LOT_ATTR05_DEFAULT_IN"].ToString(), reader["LOT_ATTR06_DEFAULT_IN"].ToString(), reader["LOT_ATTR07_DEFAULT_IN"].ToString(), reader["LOT_ATTR08_DEFAULT_IN"].ToString(), reader["LOT_ATTR09_DEFAULT_IN"].ToString(), reader["LOT_ATTR10_DEFAULT_IN"].ToString(), reader["LOT_ATTR01_DEFAULT_OT"].ToString(), reader["LOT_ATTR02_DEFAULT_OT"].ToString(), reader["LOT_ATTR03_DEFAULT_OT"].ToString(), reader["LOT_ATTR04_DEFAULT_OT"].ToString(), reader["LOT_ATTR05_DEFAULT_OT"].ToString(), reader["LOT_ATTR06_DEFAULT_OT"].ToString(), reader["LOT_ATTR07_DEFAULT_OT"].ToString(), reader["LOT_ATTR08_DEFAULT_OT"].ToString(), reader["LOT_ATTR09_DEFAULT_OT"].ToString(), reader["LOT_ATTR10_DEFAULT_OT"].ToString());
                }
            }
            return lotTemplateInfo;
        }

        public LotTemplateInfo GetLotTemplateByID(DataBase dataBase, DbTransaction tran, string lottemplateID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_LOT_TEMPLATE.Replace("@_@", whLoginID) + " WHERE LOT_TEMPLATE_ID = @LOT_TEMPLATE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT_TEMPLATE_ID", lottemplateID) };
            LotTemplateInfo lotTemplateInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                lotTemplateInfo = new LotTemplateInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOT_TEMPLATE_ID"].ToString(), reader["DESCRIPTION"].ToString(), reader["PRODUCE_DATE_VISIBLE"].ToString(), reader["PRODUCE_DATE_MANDATORY"].ToString(), reader["EXPIRY_DATE_VISIBLE"].ToString(), reader["EXPIRY_DATE_MANDATORY"].ToString(), reader["EXTERNAL_LOT_VISIBLE"].ToString(), reader["EXTERNAL_LOT_MANDATORY"].ToString(), reader["LOT_ATTR01_LABEL"].ToString(), reader["LOT_ATTR02_LABEL"].ToString(), reader["LOT_ATTR03_LABEL"].ToString(), reader["LOT_ATTR04_LABEL"].ToString(), reader["LOT_ATTR05_LABEL"].ToString(), reader["LOT_ATTR06_LABEL"].ToString(), reader["LOT_ATTR07_LABEL"].ToString(), reader["LOT_ATTR08_LABEL"].ToString(), reader["LOT_ATTR09_LABEL"].ToString(), reader["LOT_ATTR10_LABEL"].ToString(), reader["PRODUCE_DATE_LABEL"].ToString(), reader["EXPIRY_DATE_LABEL"].ToString(), reader["EXTERNAL_LOT_LABEL"].ToString(), reader["LOT_ATTR01_VISIBLE"].ToString(), reader["LOT_ATTR02_VISIBLE"].ToString(), reader["LOT_ATTR03_VISIBLE"].ToString(), reader["LOT_ATTR04_VISIBLE"].ToString(), reader["LOT_ATTR05_VISIBLE"].ToString(), reader["LOT_ATTR06_VISIBLE"].ToString(), reader["LOT_ATTR07_VISIBLE"].ToString(), reader["LOT_ATTR08_VISIBLE"].ToString(), reader["LOT_ATTR09_VISIBLE"].ToString(), reader["LOT_ATTR10_VISIBLE"].ToString(), reader["LOT_ATTR01_MANDATORY"].ToString(), reader["LOT_ATTR02_MANDATORY"].ToString(), reader["LOT_ATTR03_MANDATORY"].ToString(), reader["LOT_ATTR04_MANDATORY"].ToString(), reader["LOT_ATTR05_MANDATORY"].ToString(), reader["LOT_ATTR06_MANDATORY"].ToString(), reader["LOT_ATTR07_MANDATORY"].ToString(), reader["LOT_ATTR08_MANDATORY"].ToString(), reader["LOT_ATTR09_MANDATORY"].ToString(), reader["LOT_ATTR10_MANDATORY"].ToString(), reader["LOT_ATTR01_TYPE"].ToString(), reader["LOT_ATTR02_TYPE"].ToString(), reader["LOT_ATTR03_TYPE"].ToString(), reader["LOT_ATTR04_TYPE"].ToString(), reader["LOT_ATTR05_TYPE"].ToString(), reader["LOT_ATTR06_TYPE"].ToString(), reader["LOT_ATTR07_TYPE"].ToString(), reader["LOT_ATTR08_TYPE"].ToString(), reader["LOT_ATTR09_TYPE"].ToString(), reader["LOT_ATTR10_TYPE"].ToString(), reader["LOT_ATTR01_VALUE"].ToString(), reader["LOT_ATTR02_VALUE"].ToString(), reader["LOT_ATTR03_VALUE"].ToString(), reader["LOT_ATTR04_VALUE"].ToString(), reader["LOT_ATTR05_VALUE"].ToString(), reader["LOT_ATTR06_VALUE"].ToString(), reader["LOT_ATTR07_VALUE"].ToString(), reader["LOT_ATTR08_VALUE"].ToString(), reader["LOT_ATTR09_VALUE"].ToString(), reader["LOT_ATTR10_VALUE"].ToString(), (reader["LOT_ATTR01_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR01_SEQNO"].ToString()) : 0, (reader["LOT_ATTR02_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR02_SEQNO"].ToString()) : 0, (reader["LOT_ATTR03_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR03_SEQNO"].ToString()) : 0, (reader["LOT_ATTR04_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR04_SEQNO"].ToString()) : 0, (reader["LOT_ATTR05_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR05_SEQNO"].ToString()) : 0, (reader["LOT_ATTR06_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR06_SEQNO"].ToString()) : 0, (reader["LOT_ATTR07_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR07_SEQNO"].ToString()) : 0, (reader["LOT_ATTR08_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR08_SEQNO"].ToString()) : 0, (reader["LOT_ATTR09_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR09_SEQNO"].ToString()) : 0, (reader["LOT_ATTR10_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR10_SEQNO"].ToString()) : 0, (reader["PRODUCE_DATE_SEQNO"].ToString() != "") ? int.Parse(reader["PRODUCE_DATE_SEQNO"].ToString()) : 0, (reader["EXPIRY_DATE_SEQNO"].ToString() != "") ? int.Parse(reader["EXPIRY_DATE_SEQNO"].ToString()) : 0, (reader["EXTERNAL_LOT_SEQNO"].ToString() != "") ? int.Parse(reader["EXTERNAL_LOT_SEQNO"].ToString()) : 0, reader["IS_ACTIVE"].ToString(), reader["SYSTEM_CREATED"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["RF_LOT_ATTR01_VISIBLE"].ToString(), reader["RF_LOT_ATTR02_VISIBLE"].ToString(), reader["RF_LOT_ATTR03_VISIBLE"].ToString(), reader["RF_LOT_ATTR04_VISIBLE"].ToString(), reader["RF_LOT_ATTR05_VISIBLE"].ToString(), reader["RF_LOT_ATTR06_VISIBLE"].ToString(), reader["RF_LOT_ATTR07_VISIBLE"].ToString(), reader["RF_LOT_ATTR08_VISIBLE"].ToString(), reader["RF_LOT_ATTR09_VISIBLE"].ToString(), reader["RF_LOT_ATTR10_VISIBLE"].ToString(), reader["RF_PRODUCE_DATE_VISIBLE"].ToString(), reader["RF_EXPIRY_DATE_VISIBLE"].ToString(), reader["RF_EXTERNAL_LOT_VISIBLE"].ToString(), reader["LOT_ATTR01_MANDATORY_OT"].ToString(), reader["LOT_ATTR02_MANDATORY_OT"].ToString(), reader["LOT_ATTR03_MANDATORY_OT"].ToString(), reader["LOT_ATTR04_MANDATORY_OT"].ToString(), reader["LOT_ATTR05_MANDATORY_OT"].ToString(), reader["LOT_ATTR06_MANDATORY_OT"].ToString(), reader["LOT_ATTR07_MANDATORY_OT"].ToString(), reader["LOT_ATTR08_MANDATORY_OT"].ToString(), reader["LOT_ATTR09_MANDATORY_OT"].ToString(), reader["LOT_ATTR10_MANDATORY_OT"].ToString(), reader["PRODUCE_DATE_MANDATORY_OT"].ToString(), reader["EXPIRY_DATE_MANDATORY_OT"].ToString(), reader["EXTERNAL_LOT_MANDATORY_OT"].ToString(), reader["LOT_ATTR01_DEFAULT_IN"].ToString(), reader["LOT_ATTR02_DEFAULT_IN"].ToString(), reader["LOT_ATTR03_DEFAULT_IN"].ToString(), reader["LOT_ATTR04_DEFAULT_IN"].ToString(), reader["LOT_ATTR05_DEFAULT_IN"].ToString(), reader["LOT_ATTR06_DEFAULT_IN"].ToString(), reader["LOT_ATTR07_DEFAULT_IN"].ToString(), reader["LOT_ATTR08_DEFAULT_IN"].ToString(), reader["LOT_ATTR09_DEFAULT_IN"].ToString(), reader["LOT_ATTR10_DEFAULT_IN"].ToString(), reader["LOT_ATTR01_DEFAULT_OT"].ToString(), reader["LOT_ATTR02_DEFAULT_OT"].ToString(), reader["LOT_ATTR03_DEFAULT_OT"].ToString(), reader["LOT_ATTR04_DEFAULT_OT"].ToString(), reader["LOT_ATTR05_DEFAULT_OT"].ToString(), reader["LOT_ATTR06_DEFAULT_OT"].ToString(), reader["LOT_ATTR07_DEFAULT_OT"].ToString(), reader["LOT_ATTR08_DEFAULT_OT"].ToString(), reader["LOT_ATTR09_DEFAULT_OT"].ToString(), reader["LOT_ATTR10_DEFAULT_OT"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return lotTemplateInfo;
        }

        public DataSet GetLotTemplateByQueryList(List<string> sqlWhere, LotTemplateQueryEntity lottemplateQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_LOT_TEMPLATE.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!lottemplateQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, lottemplateQuery.CurrentPage, lottemplateQuery.PageSize, lottemplateQuery.SortField, lottemplateQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetLotTemplateByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, LotTemplateQueryEntity lottemplateQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_LOT_TEMPLATE.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            temp = PagingHelper.GetPagingSQL(temp, lottemplateQuery.CurrentPage, lottemplateQuery.PageSize, lottemplateQuery.SortField, lottemplateQuery.SortDirection);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertLotTemplate(LotTemplateInfo lotTemplateInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    lotTemplateInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_LotTemplate_Parameters(lotTemplateInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_LOT_TEMPLATE.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertLotTemplate(DataBase dataBase, DbTransaction tran, LotTemplateInfo lotTemplateInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    lotTemplateInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_LotTemplate_Parameters(lotTemplateInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_LOT_TEMPLATE.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_LotTemplate_Parameters(LotTemplateInfo lotTemplateInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.LOT_TEMPLATE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.DESCRIPTION, SqlDbType.VarChar, 100), new SqlParameter(this.PRODUCE_DATE_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.PRODUCE_DATE_MANDATORY, SqlDbType.VarChar, 1), new SqlParameter(this.EXPIRY_DATE_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.EXPIRY_DATE_MANDATORY, SqlDbType.VarChar, 1), new SqlParameter(this.EXTERNAL_LOT_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.EXTERNAL_LOT_MANDATORY, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR01_LABEL, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR02_LABEL, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR03_LABEL, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR04_LABEL, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR05_LABEL, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR06_LABEL, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR07_LABEL, SqlDbType.VarChar, 20), 
                new SqlParameter(this.LOT_ATTR08_LABEL, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR09_LABEL, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR10_LABEL, SqlDbType.VarChar, 20), new SqlParameter(this.PRODUCE_DATE_LABEL, SqlDbType.VarChar, 20), new SqlParameter(this.EXPIRY_DATE_LABEL, SqlDbType.VarChar, 20), new SqlParameter(this.EXTERNAL_LOT_LABEL, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR01_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR02_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR03_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR04_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR05_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR06_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR07_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR08_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR09_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR10_VISIBLE, SqlDbType.VarChar, 1), 
                new SqlParameter(this.LOT_ATTR01_MANDATORY, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR02_MANDATORY, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR03_MANDATORY, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR04_MANDATORY, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR05_MANDATORY, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR06_MANDATORY, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR07_MANDATORY, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR08_MANDATORY, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR09_MANDATORY, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR10_MANDATORY, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR01_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR02_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR03_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR04_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR05_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR06_TYPE, SqlDbType.VarChar, 20), 
                new SqlParameter(this.LOT_ATTR07_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR08_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR09_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR10_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR01_VALUE, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR02_VALUE, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR03_VALUE, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR04_VALUE, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR05_VALUE, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR06_VALUE, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR07_VALUE, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR08_VALUE, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR09_VALUE, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR10_VALUE, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR01_SEQNO, SqlDbType.Int, 4), new SqlParameter(this.LOT_ATTR02_SEQNO, SqlDbType.Int, 4), 
                new SqlParameter(this.LOT_ATTR03_SEQNO, SqlDbType.Int, 4), new SqlParameter(this.LOT_ATTR04_SEQNO, SqlDbType.Int, 4), new SqlParameter(this.LOT_ATTR05_SEQNO, SqlDbType.Int, 4), new SqlParameter(this.LOT_ATTR06_SEQNO, SqlDbType.Int, 4), new SqlParameter(this.LOT_ATTR07_SEQNO, SqlDbType.Int, 4), new SqlParameter(this.LOT_ATTR08_SEQNO, SqlDbType.Int, 4), new SqlParameter(this.LOT_ATTR09_SEQNO, SqlDbType.Int, 4), new SqlParameter(this.LOT_ATTR10_SEQNO, SqlDbType.Int, 4), new SqlParameter(this.PRODUCE_DATE_SEQNO, SqlDbType.Int, 4), new SqlParameter(this.EXPIRY_DATE_SEQNO, SqlDbType.Int, 4), new SqlParameter(this.EXTERNAL_LOT_SEQNO, SqlDbType.Int, 4), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.SYSTEM_CREATED, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 0x12), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 0x12), new SqlParameter(this.RF_LOT_ATTR01_VISIBLE, SqlDbType.VarChar, 1), 
                new SqlParameter(this.RF_LOT_ATTR02_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.RF_LOT_ATTR03_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.RF_LOT_ATTR04_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.RF_LOT_ATTR05_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.RF_LOT_ATTR06_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.RF_LOT_ATTR07_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.RF_LOT_ATTR08_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.RF_LOT_ATTR09_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.RF_LOT_ATTR10_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.RF_PRODUCE_DATE_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.RF_EXPIRY_DATE_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.RF_EXTERNAL_LOT_VISIBLE, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR01_MANDATORY_OT, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR02_MANDATORY_OT, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR03_MANDATORY_OT, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR04_MANDATORY_OT, SqlDbType.VarChar, 1), 
                new SqlParameter(this.LOT_ATTR05_MANDATORY_OT, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR06_MANDATORY_OT, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR07_MANDATORY_OT, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR08_MANDATORY_OT, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR09_MANDATORY_OT, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR10_MANDATORY_OT, SqlDbType.VarChar, 1), new SqlParameter(this.PRODUCE_DATE_MANDATORY_OT, SqlDbType.VarChar, 1), new SqlParameter(this.EXPIRY_DATE_MANDATORY_OT, SqlDbType.VarChar, 1), new SqlParameter(this.EXTERNAL_LOT_MANDATORY_OT, SqlDbType.VarChar, 1), new SqlParameter(this.LOT_ATTR01_DEFAULT_IN, SqlDbType.VarChar, 30), new SqlParameter(this.LOT_ATTR02_DEFAULT_IN, SqlDbType.VarChar, 30), new SqlParameter(this.LOT_ATTR03_DEFAULT_IN, SqlDbType.VarChar, 30), new SqlParameter(this.LOT_ATTR04_DEFAULT_IN, SqlDbType.VarChar, 30), new SqlParameter(this.LOT_ATTR05_DEFAULT_IN, SqlDbType.VarChar, 30), new SqlParameter(this.LOT_ATTR06_DEFAULT_IN, SqlDbType.VarChar, 30), new SqlParameter(this.LOT_ATTR07_DEFAULT_IN, SqlDbType.VarChar, 30), 
                new SqlParameter(this.LOT_ATTR08_DEFAULT_IN, SqlDbType.VarChar, 30), new SqlParameter(this.LOT_ATTR09_DEFAULT_IN, SqlDbType.VarChar, 30), new SqlParameter(this.LOT_ATTR10_DEFAULT_IN, SqlDbType.VarChar, 30), new SqlParameter(this.LOT_ATTR01_DEFAULT_OT, SqlDbType.VarChar, 30), new SqlParameter(this.LOT_ATTR02_DEFAULT_OT, SqlDbType.VarChar, 30), new SqlParameter(this.LOT_ATTR03_DEFAULT_OT, SqlDbType.VarChar, 30), new SqlParameter(this.LOT_ATTR04_DEFAULT_OT, SqlDbType.VarChar, 30), new SqlParameter(this.LOT_ATTR05_DEFAULT_OT, SqlDbType.VarChar, 30), new SqlParameter(this.LOT_ATTR06_DEFAULT_OT, SqlDbType.VarChar, 30), new SqlParameter(this.LOT_ATTR07_DEFAULT_OT, SqlDbType.VarChar, 30), new SqlParameter(this.LOT_ATTR08_DEFAULT_OT, SqlDbType.VarChar, 30), new SqlParameter(this.LOT_ATTR09_DEFAULT_OT, SqlDbType.VarChar, 30), new SqlParameter(this.LOT_ATTR10_DEFAULT_OT, SqlDbType.VarChar, 30)
             };
            if (!string.IsNullOrEmpty(lotTemplateInfo.WhID))
            {
                paramArray[0].Value = lotTemplateInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = lotTemplateInfo.LotTemplateID;
            paramArray[2].Value = lotTemplateInfo.Description;
            paramArray[3].Value = lotTemplateInfo.ProduceDateVisible;
            paramArray[4].Value = lotTemplateInfo.ProduceDateMandatory;
            paramArray[5].Value = lotTemplateInfo.ExpiryDateVisible;
            paramArray[6].Value = lotTemplateInfo.ExpiryDateMandatory;
            paramArray[7].Value = lotTemplateInfo.ExternalLotVisible;
            paramArray[8].Value = lotTemplateInfo.ExternalLotMandatory;
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr01Label))
            {
                paramArray[9].Value = lotTemplateInfo.LotAttr01Label;
            }
            else
            {
                paramArray[9].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr02Label))
            {
                paramArray[10].Value = lotTemplateInfo.LotAttr02Label;
            }
            else
            {
                paramArray[10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr03Label))
            {
                paramArray[11].Value = lotTemplateInfo.LotAttr03Label;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr04Label))
            {
                paramArray[12].Value = lotTemplateInfo.LotAttr04Label;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr05Label))
            {
                paramArray[13].Value = lotTemplateInfo.LotAttr05Label;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr06Label))
            {
                paramArray[14].Value = lotTemplateInfo.LotAttr06Label;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr07Label))
            {
                paramArray[15].Value = lotTemplateInfo.LotAttr07Label;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr08Label))
            {
                paramArray[0x10].Value = lotTemplateInfo.LotAttr08Label;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr09Label))
            {
                paramArray[0x11].Value = lotTemplateInfo.LotAttr09Label;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr10Label))
            {
                paramArray[0x12].Value = lotTemplateInfo.LotAttr10Label;
            }
            else
            {
                paramArray[0x12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.ProduceDateLabel))
            {
                paramArray[0x13].Value = lotTemplateInfo.ProduceDateLabel;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.ExpiryDateLabel))
            {
                paramArray[20].Value = lotTemplateInfo.ExpiryDateLabel;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.ExternalLotLabel))
            {
                paramArray[0x15].Value = lotTemplateInfo.ExternalLotLabel;
            }
            else
            {
                paramArray[0x15].Value = DBNull.Value;
            }
            paramArray[0x16].Value = lotTemplateInfo.LotAttr01Visible;
            paramArray[0x17].Value = lotTemplateInfo.LotAttr02Visible;
            paramArray[0x18].Value = lotTemplateInfo.LotAttr03Visible;
            paramArray[0x19].Value = lotTemplateInfo.LotAttr04Visible;
            paramArray[0x1a].Value = lotTemplateInfo.LotAttr05Visible;
            paramArray[0x1b].Value = lotTemplateInfo.LotAttr06Visible;
            paramArray[0x1c].Value = lotTemplateInfo.LotAttr07Visible;
            paramArray[0x1d].Value = lotTemplateInfo.LotAttr08Visible;
            paramArray[30].Value = lotTemplateInfo.LotAttr09Visible;
            paramArray[0x1f].Value = lotTemplateInfo.LotAttr10Visible;
            paramArray[0x20].Value = lotTemplateInfo.LotAttr01Mandatory;
            paramArray[0x21].Value = lotTemplateInfo.LotAttr02Mandatory;
            paramArray[0x22].Value = lotTemplateInfo.LotAttr03Mandatory;
            paramArray[0x23].Value = lotTemplateInfo.LotAttr04Mandatory;
            paramArray[0x24].Value = lotTemplateInfo.LotAttr05Mandatory;
            paramArray[0x25].Value = lotTemplateInfo.LotAttr06Mandatory;
            paramArray[0x26].Value = lotTemplateInfo.LotAttr07Mandatory;
            paramArray[0x27].Value = lotTemplateInfo.LotAttr08Mandatory;
            paramArray[40].Value = lotTemplateInfo.LotAttr09Mandatory;
            paramArray[0x29].Value = lotTemplateInfo.LotAttr10Mandatory;
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr01Type))
            {
                paramArray[0x2a].Value = lotTemplateInfo.LotAttr01Type;
            }
            else
            {
                paramArray[0x2a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr02Type))
            {
                paramArray[0x2b].Value = lotTemplateInfo.LotAttr02Type;
            }
            else
            {
                paramArray[0x2b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr03Type))
            {
                paramArray[0x2c].Value = lotTemplateInfo.LotAttr03Type;
            }
            else
            {
                paramArray[0x2c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr04Type))
            {
                paramArray[0x2d].Value = lotTemplateInfo.LotAttr04Type;
            }
            else
            {
                paramArray[0x2d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr05Type))
            {
                paramArray[0x2e].Value = lotTemplateInfo.LotAttr05Type;
            }
            else
            {
                paramArray[0x2e].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr06Type))
            {
                paramArray[0x2f].Value = lotTemplateInfo.LotAttr06Type;
            }
            else
            {
                paramArray[0x2f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr07Type))
            {
                paramArray[0x30].Value = lotTemplateInfo.LotAttr07Type;
            }
            else
            {
                paramArray[0x30].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr08Type))
            {
                paramArray[0x31].Value = lotTemplateInfo.LotAttr08Type;
            }
            else
            {
                paramArray[0x31].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr09Type))
            {
                paramArray[50].Value = lotTemplateInfo.LotAttr09Type;
            }
            else
            {
                paramArray[50].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr10Type))
            {
                paramArray[0x33].Value = lotTemplateInfo.LotAttr10Type;
            }
            else
            {
                paramArray[0x33].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr01Value))
            {
                paramArray[0x34].Value = lotTemplateInfo.LotAttr01Value;
            }
            else
            {
                paramArray[0x34].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr02Value))
            {
                paramArray[0x35].Value = lotTemplateInfo.LotAttr02Value;
            }
            else
            {
                paramArray[0x35].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr03Value))
            {
                paramArray[0x36].Value = lotTemplateInfo.LotAttr03Value;
            }
            else
            {
                paramArray[0x36].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr04Value))
            {
                paramArray[0x37].Value = lotTemplateInfo.LotAttr04Value;
            }
            else
            {
                paramArray[0x37].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr05Value))
            {
                paramArray[0x38].Value = lotTemplateInfo.LotAttr05Value;
            }
            else
            {
                paramArray[0x38].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr06Value))
            {
                paramArray[0x39].Value = lotTemplateInfo.LotAttr06Value;
            }
            else
            {
                paramArray[0x39].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr07Value))
            {
                paramArray[0x3a].Value = lotTemplateInfo.LotAttr07Value;
            }
            else
            {
                paramArray[0x3a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr08Value))
            {
                paramArray[0x3b].Value = lotTemplateInfo.LotAttr08Value;
            }
            else
            {
                paramArray[0x3b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr09Value))
            {
                paramArray[60].Value = lotTemplateInfo.LotAttr09Value;
            }
            else
            {
                paramArray[60].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr10Value))
            {
                paramArray[0x3d].Value = lotTemplateInfo.LotAttr10Value;
            }
            else
            {
                paramArray[0x3d].Value = DBNull.Value;
            }
            paramArray[0x3e].Value = lotTemplateInfo.LotAttr01Seqno;
            paramArray[0x3f].Value = lotTemplateInfo.LotAttr02Seqno;
            paramArray[0x40].Value = lotTemplateInfo.LotAttr03Seqno;
            paramArray[0x41].Value = lotTemplateInfo.LotAttr04Seqno;
            paramArray[0x42].Value = lotTemplateInfo.LotAttr05Seqno;
            paramArray[0x43].Value = lotTemplateInfo.LotAttr06Seqno;
            paramArray[0x44].Value = lotTemplateInfo.LotAttr07Seqno;
            paramArray[0x45].Value = lotTemplateInfo.LotAttr08Seqno;
            paramArray[70].Value = lotTemplateInfo.LotAttr09Seqno;
            paramArray[0x47].Value = lotTemplateInfo.LotAttr10Seqno;
            paramArray[0x48].Value = lotTemplateInfo.ProduceDateSeqno;
            paramArray[0x49].Value = lotTemplateInfo.ExpiryDateSeqno;
            paramArray[0x4a].Value = lotTemplateInfo.ExternalLotSeqno;
            paramArray[0x4b].Value = lotTemplateInfo.IsActive;
            paramArray[0x4c].Value = lotTemplateInfo.SystemCreated;
            paramArray[0x4d].Value = lotTemplateInfo.CreatedBy;
            paramArray[0x4e].Value = lotTemplateInfo.UpdatedBy;
            if (!string.IsNullOrEmpty(lotTemplateInfo.RfLotAttr01Visible))
            {
                paramArray[0x4f].Value = lotTemplateInfo.RfLotAttr01Visible;
            }
            else
            {
                paramArray[0x4f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.RfLotAttr02Visible))
            {
                paramArray[80].Value = lotTemplateInfo.RfLotAttr02Visible;
            }
            else
            {
                paramArray[80].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.RfLotAttr03Visible))
            {
                paramArray[0x51].Value = lotTemplateInfo.RfLotAttr03Visible;
            }
            else
            {
                paramArray[0x51].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.RfLotAttr04Visible))
            {
                paramArray[0x52].Value = lotTemplateInfo.RfLotAttr04Visible;
            }
            else
            {
                paramArray[0x52].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.RfLotAttr05Visible))
            {
                paramArray[0x53].Value = lotTemplateInfo.RfLotAttr05Visible;
            }
            else
            {
                paramArray[0x53].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.RfLotAttr06Visible))
            {
                paramArray[0x54].Value = lotTemplateInfo.RfLotAttr06Visible;
            }
            else
            {
                paramArray[0x54].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.RfLotAttr07Visible))
            {
                paramArray[0x55].Value = lotTemplateInfo.RfLotAttr07Visible;
            }
            else
            {
                paramArray[0x55].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.RfLotAttr08Visible))
            {
                paramArray[0x56].Value = lotTemplateInfo.RfLotAttr08Visible;
            }
            else
            {
                paramArray[0x56].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.RfLotAttr09Visible))
            {
                paramArray[0x57].Value = lotTemplateInfo.RfLotAttr09Visible;
            }
            else
            {
                paramArray[0x57].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.RfLotAttr10Visible))
            {
                paramArray[0x58].Value = lotTemplateInfo.RfLotAttr10Visible;
            }
            else
            {
                paramArray[0x58].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.RfProduceDateVisible))
            {
                paramArray[0x59].Value = lotTemplateInfo.RfProduceDateVisible;
            }
            else
            {
                paramArray[0x59].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.RfExpiryDateVisible))
            {
                paramArray[90].Value = lotTemplateInfo.RfExpiryDateVisible;
            }
            else
            {
                paramArray[90].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.RfExternalLotVisible))
            {
                paramArray[0x5b].Value = lotTemplateInfo.RfExternalLotVisible;
            }
            else
            {
                paramArray[0x5b].Value = DBNull.Value;
            }
            paramArray[0x5c].Value = lotTemplateInfo.LotAttr01MandatoryOT;
            paramArray[0x5d].Value = lotTemplateInfo.LotAttr02MandatoryOT;
            paramArray[0x5e].Value = lotTemplateInfo.LotAttr03MandatoryOT;
            paramArray[0x5f].Value = lotTemplateInfo.LotAttr04MandatoryOT;
            paramArray[0x60].Value = lotTemplateInfo.LotAttr05MandatoryOT;
            paramArray[0x61].Value = lotTemplateInfo.LotAttr06MandatoryOT;
            paramArray[0x62].Value = lotTemplateInfo.LotAttr07MandatoryOT;
            paramArray[0x63].Value = lotTemplateInfo.LotAttr08MandatoryOT;
            paramArray[100].Value = lotTemplateInfo.LotAttr09MandatoryOT;
            paramArray[0x65].Value = lotTemplateInfo.LotAttr10MandatoryOT;
            paramArray[0x66].Value = lotTemplateInfo.ProduceDateMandatoryOT;
            paramArray[0x67].Value = lotTemplateInfo.ExpiryDateMandatoryOT;
            paramArray[0x68].Value = lotTemplateInfo.ExternalLotMandatoryOT;
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr01ValueDefaultIN))
            {
                paramArray[0x69].Value = lotTemplateInfo.LotAttr01ValueDefaultIN;
            }
            else
            {
                paramArray[0x69].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr02ValueDefaultIN))
            {
                paramArray[0x6a].Value = lotTemplateInfo.LotAttr02ValueDefaultIN;
            }
            else
            {
                paramArray[0x6a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr03ValueDefaultIN))
            {
                paramArray[0x6b].Value = lotTemplateInfo.LotAttr03ValueDefaultIN;
            }
            else
            {
                paramArray[0x6b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr04ValueDefaultIN))
            {
                paramArray[0x6c].Value = lotTemplateInfo.LotAttr04ValueDefaultIN;
            }
            else
            {
                paramArray[0x6c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr05ValueDefaultIN))
            {
                paramArray[0x6d].Value = lotTemplateInfo.LotAttr05ValueDefaultIN;
            }
            else
            {
                paramArray[0x6d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr06ValueDefaultIN))
            {
                paramArray[110].Value = lotTemplateInfo.LotAttr06ValueDefaultIN;
            }
            else
            {
                paramArray[110].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr07ValueDefaultIN))
            {
                paramArray[0x6f].Value = lotTemplateInfo.LotAttr07ValueDefaultIN;
            }
            else
            {
                paramArray[0x6f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr08ValueDefaultIN))
            {
                paramArray[0x70].Value = lotTemplateInfo.LotAttr08ValueDefaultIN;
            }
            else
            {
                paramArray[0x70].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr09ValueDefaultIN))
            {
                paramArray[0x71].Value = lotTemplateInfo.LotAttr09ValueDefaultIN;
            }
            else
            {
                paramArray[0x71].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr10ValueDefaultIN))
            {
                paramArray[0x72].Value = lotTemplateInfo.LotAttr10ValueDefaultIN;
            }
            else
            {
                paramArray[0x72].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr01ValueDefaultOT))
            {
                paramArray[0x73].Value = lotTemplateInfo.LotAttr01ValueDefaultOT;
            }
            else
            {
                paramArray[0x73].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr02ValueDefaultOT))
            {
                paramArray[0x74].Value = lotTemplateInfo.LotAttr02ValueDefaultOT;
            }
            else
            {
                paramArray[0x74].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr03ValueDefaultOT))
            {
                paramArray[0x75].Value = lotTemplateInfo.LotAttr03ValueDefaultOT;
            }
            else
            {
                paramArray[0x75].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr04ValueDefaultOT))
            {
                paramArray[0x76].Value = lotTemplateInfo.LotAttr04ValueDefaultOT;
            }
            else
            {
                paramArray[0x76].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr05ValueDefaultOT))
            {
                paramArray[0x77].Value = lotTemplateInfo.LotAttr05ValueDefaultOT;
            }
            else
            {
                paramArray[0x77].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr06ValueDefaultOT))
            {
                paramArray[120].Value = lotTemplateInfo.LotAttr06ValueDefaultOT;
            }
            else
            {
                paramArray[120].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr07ValueDefaultOT))
            {
                paramArray[0x79].Value = lotTemplateInfo.LotAttr07ValueDefaultOT;
            }
            else
            {
                paramArray[0x79].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr08ValueDefaultOT))
            {
                paramArray[0x7a].Value = lotTemplateInfo.LotAttr08ValueDefaultOT;
            }
            else
            {
                paramArray[0x7a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr09ValueDefaultOT))
            {
                paramArray[0x7b].Value = lotTemplateInfo.LotAttr09ValueDefaultOT;
            }
            else
            {
                paramArray[0x7b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotTemplateInfo.LotAttr10ValueDefaultOT))
            {
                paramArray[0x7c].Value = lotTemplateInfo.LotAttr10ValueDefaultOT;
                return paramArray;
            }
            paramArray[0x7c].Value = DBNull.Value;
            return paramArray;
        }

        public int UpdateLotTemplate(LotTemplateInfo lotTemplateInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    lotTemplateInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_LotTemplate_Parameters(lotTemplateInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_LOT_TEMPLATE.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateLotTemplate(DataBase dataBase, DbTransaction tran, LotTemplateInfo lotTemplateInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    lotTemplateInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_LotTemplate_Parameters(lotTemplateInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_LOT_TEMPLATE.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

